﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Web;
using System.Web.Mvc;
using DHQGHN.Common;
using DHQGHN.DAL.BO.MappingDB;
using DHQGHN.DAL.BO.Metadata;
using DHQGHN.DAL.Models.MappingDB;
using DHQGHN.PM0.Common;
using DHQGHN.DAL.Models;
using DHQGHN.DAL.Models.Metadata;
using DHQGHN.PM0.Models.Metadata;
using DHQGHN.MVC.Models.Admin;

namespace DHQGHN.PM0.Controllers
{
    public class ConfigController : BaseController
    {
        log4net.ILog logger = log4net.LogManager.GetLogger(typeof(ConfigController));

        #region Chuc nang cau hinh source

        public ActionResult ConfigSource()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[241] = "active";
            ViewBag.ActiveMenus[243] = "active";

            try
            {
                SD_ConfigSourceBO bo = new SD_ConfigSourceBO();
                SD_ConfigSource sdConfigSource = bo.GetLatestConfigSource();
                if (sdConfigSource == null)
                {

                    sdConfigSource = new SD_ConfigSource();
                    sdConfigSource.DataSource = "SQL Server Native Client 10.0";
                }

                BindSystemDatabaseToDatabaseList();
                return View(sdConfigSource);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        private void BindSystemDatabaseToDatabaseList()
        {
            try
            {
                DatabasesBO databasesBO = new DatabasesBO();
                List<DatabaseModel> databaseList = databasesBO.GetAll();

                DatabaseModel objEditDatabase = new DatabaseModel();
                List<SelectListItem> lst = new List<SelectListItem>();
                lst.Add(
                    new SelectListItem
                    {
                        Text = "Chọn",
                        Value = DBNull.Value.ToString()
                    });

                foreach (DatabaseModel database in databaseList)
                {
                    lst.Add(new SelectListItem()
                    {
                        Text = database.Name,
                        Value = database.Name

                    });
                }

                ViewBag.LstDatabase = lst;
            }
            catch { throw; }
        }


        public ActionResult BindDataToDatabaseListAjax(string servername, string username, string password)
        {
            List<SelectListItem> lst = new List<SelectListItem>();
            try
            {
                string servernameDecoded = HttpUtility.UrlDecode(servername);
                string userNameDecoded = HttpUtility.UrlDecode(username);
                string passwordDecoded = HttpUtility.UrlDecode(password);

                var connectionString = Utils.BuildConnectionString(servernameDecoded, userNameDecoded, passwordDecoded);

                DatabasesBO databasesBO = new DatabasesBO(connectionString);
                List<DatabaseModel> databaseList = databasesBO.GetAll();

                lst.Add(
                    new SelectListItem
                    {
                        Text = "Chọn",
                        Value = DBNull.Value.ToString()
                    });

                if (databaseList != null)
                {
                    foreach (DatabaseModel database in databaseList)
                    {
                        lst.Add(new SelectListItem()
                        {
                            Text = database.Name,
                            Value = database.Name

                        });
                    }
                }

                //ViewBag.LstDatabase = lst;
                return ReturnValue("Thành công", true, lst);

            }
            catch (Exception e)
            {
                logger.Error(e.ToString());
                return ReturnValue(e.Message, false, lst);
            }
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        [ValidateInput(false)]
        public ActionResult ConfigSource(SD_ConfigSource model)
        {
            try
            {
                // TODO: Add update logic here
                if (ModelState.IsValid)
                {
                    SD_ConfigSourceBO bo = new SD_ConfigSourceBO();
                    if (Utils.IntIsNullOrZeRo(model.ConfigSourceID))
                    {
                        bo.Save(model);

                        Notice.Show(Utils.GetString("Config_Source_InsertSuccees_Inform"), NoticeType.Success);
                    }
                    else
                    {
                        bo.Save(model);
                        Notice.Show(Utils.GetString("Config_Source_UpdateSuccees_Inform"), NoticeType.Success);
                    }
                }
                BindSystemDatabaseToDatabaseList();
                return View(model);
            }
            catch (Exception ex)
            {
                ViewBag.Success = false;
                logger.Error(ex.ToString());
                return View(model);
            }
        }
        #endregion

        #region Chuc nang cau hinh staging db
        public ActionResult ConfigStaging()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[241] = "active";
            ViewBag.ActiveMenus[244] = "active";

            try
            {
                SD_ConfigDestinationBO bo = new SD_ConfigDestinationBO();
                SD_ConfigDestination sdConfigSource = bo.GetLatestConfigSource();
                if (sdConfigSource == null)
                {
                    sdConfigSource = new SD_ConfigDestination();
                    sdConfigSource.DataSource = "SQL Server Native Client 10.0";
                }

                BindSystemDatabaseToDatabaseList();

                return View(sdConfigSource);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return View();
            }
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        [ValidateInput(false)]
        public ActionResult ConfigStaging(SD_ConfigDestination model)
        {
            try
            {
                // TODO: Add update logic here
                if (ModelState.IsValid)
                {
                    SD_ConfigDestinationBO bo = new SD_ConfigDestinationBO();
                    if (Utils.IntIsNullOrZeRo(model.ConfigDestinationID))
                    {
                        bo.Save(model);

                        Notice.Show(Utils.GetString("Config_Destination_InsertSuccees_Inform"), NoticeType.Success);
                    }
                    else
                    {
                        bo.Save(model);
                        Notice.Show(Utils.GetString("Config_Destination_UpdateSuccees_Inform"), NoticeType.Success);
                    }
                }
                BindSystemDatabaseToDatabaseList();
                return View(model);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                ViewBag.Success = false;
                return View(model);
            }
        }

        #endregion

        #region Chuc nang cau hinh mapping DatabaseName
        public ActionResult MappingDatabaseTable()
        {

            return View();
        }

        public ActionResult ShowMappingColumn(int id)
        {
            try
            {
                this.IsAjaxRequest();
                SD_ConfigSource sourceDB = new SD_ConfigSourceBO().GetLatestConfigSource();
                SD_ConfigDestination destinationDB = new SD_ConfigDestinationBO().GetLatestConfigSource();
                //Get connectionString
                string sourceConString = Utils.BuildConnectionString(sourceDB.ServerName, sourceDB.DataBaseName, sourceDB.sUser, sourceDB.sPassWord);
                string destinationString = Utils.BuildConnectionString(destinationDB.ServerName, destinationDB.DataBaseName, destinationDB.sUser, destinationDB.sPassWord);

                SD_MappingTableBO tableBO = new SD_MappingTableBO();
                SD_MappingTable model = tableBO.GetMappingTableById(id);

                SD_MappingColumnBO columnBO = new SD_MappingColumnBO(sourceConString);
                //List<SD_MappingColumn> sourceMappingColumnList = columnBO.GetAllColumnInfoByMappingTableId(model.MappingTableId);

                List<SD_MappingColumn> sourceColumnList = columnBO.GetAllColumnInfoMetadataByTableName(model.MappingTableId, model.SourceTable);

                columnBO = new SD_MappingColumnBO();
                SD_MappingColumn mappingColumn = null;
                foreach (SD_MappingColumn sourceColumn in sourceColumnList)
                {
                    //sdMappingTableBO.GetSD_Mapping
                    mappingColumn = columnBO.GetColumnMappingBySouceColumnName(sourceColumn.MappingTableId, sourceColumn.SourceColumnName);
                    if (mappingColumn != null)
                    {
                        sourceColumn.MappingColumnId = mappingColumn.MappingColumnId;
                        sourceColumn.Checked = true;
                        sourceColumn.DestinationColumnName = mappingColumn.DestinationColumnName;
                        sourceColumn.DestinationColumnNull = mappingColumn.DestinationColumnNull;
                        sourceColumn.DestinationColumnPrecision = mappingColumn.DestinationColumnPrecision;
                        sourceColumn.DestinationColumnScale = mappingColumn.DestinationColumnScale;
                        sourceColumn.DestinationColumnSize = mappingColumn.DestinationColumnSize;
                        sourceColumn.DestinationColumnType = mappingColumn.DestinationColumnType;
                        sourceColumn.DestinationTable = mappingColumn.DestinationTable;

                    }
                }


                ViewBag.AreaList = sourceColumnList;

                return PartialView("_MappingColumn", model);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return ReturnValue(ex.Message, false);
            }
        }

        public ActionResult ShowData(string databaseName, string tableName, int pageIndex)
        {
            try
            {
                this.IsAjaxRequest();
                //Get connectionString
                SD_ConfigSource sourceDB = new SD_ConfigSourceBO().GetLatestConfigSource();
                string sourceConString = Utils.BuildConnectionString(sourceDB.ServerName, sourceDB.DataBaseName, sourceDB.sUser, sourceDB.sPassWord);
                string enabledPagingServer = System.Configuration.ConfigurationManager.AppSettings["ENABLED_PAGING_SERVER"].ToString();
                if (enabledPagingServer.Contains(sourceDB.ServerName))
                {
                    var model = search(databaseName, tableName, pageIndex, 10);
                    ViewBag.DatabaseName = databaseName;
                    ViewBag.TableName = tableName;
                    return PartialView("_DataTable", model);
                }
                else
                {
                    DataBO dataBO = new DataBO();
                    DataTable dt = dataBO.GetTop10Data(databaseName, tableName);
                    ViewBag.Top10Data = dt;
                    return PartialView("_DataTableTop10");
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return ReturnValue(ex.Message, false);
            }
        }

        [HttpPost]
        public ActionResult ActionSearch(string databaseName, string tableName, int pageIndex = 0, int pageSize = 10)
        {
            try
            {
                var model = search(databaseName, tableName, pageIndex, 10);
                ViewBag.TableName = tableName;
                return PartialView("_DataTablePart", model);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        private DHQGHN.DAL.Common.DataPage<DataRow> search(string databaseName, string tableName, int pageIndex, int pageSize)
        {
            try
            {
                DataBO dataBO = new DataBO();

                DHQGHN.DAL.Common.DataPage<DataRow> model = dataBO.GetAllData(databaseName, tableName, pageIndex, pageSize);

                if (model.Data.Count > 0)
                {
                    //Tao component phan trang
                    var pagNative = new DHQGHN.DAL.Common.Pagination
                    {
                        ActionName = "actionSearch",
                        ModelName = "Transfer",
                        CurrentPage = pageIndex,
                        InputSearchId = "",
                        TagetReLoadId = "mappingdata-list",

                    };
                    pagNative.TotalRows = model.RecordCount;
                    pagNative.CurrentRow = model.Data.Count;
                    ViewBag.Pagination = pagNative;
                }

                return model;
            }
            catch
            {
                throw;
            }
        }


        const int TAO_MOI = 1;
        const int XOA = 2;

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult SubmitChangeMappingColumn(List<SD_MappingColumn> lstMapping, int MappingTableId, string destinationTable)
        {
            try
            {
                SD_MappingTableBO bo = new SD_MappingTableBO();
                SD_MappingTable mappingTable = bo.GetMappingTableById(MappingTableId);
                mappingTable.DestinationTable = destinationTable;
                bo.Save(mappingTable);

                SD_MappingColumnBO columnBO = new SD_MappingColumnBO();
                List<SD_MappingColumn> curSdMappingColumnList = columnBO.GetAllColumnInfoByMappingTableId(MappingTableId);

                // Thêm mới
                foreach (SD_MappingColumn column in lstMapping)
                {
                    if (column.Checked && column.MappingColumnId <= 0)
                    {
                        column.MappingTableId = MappingTableId;
                        column.ConfigSourceID = mappingTable.ConfigSourceID;
                        column.ConfigDestinationID = mappingTable.ConfigDestinationID;
                        column.SourceTable = mappingTable.SourceTable;
                        columnBO.Save(column);
                    }

                }


                // Cập nhật và xóa
                foreach (SD_MappingColumn column in curSdMappingColumnList)
                {
                    // Tìm cột đã tồn tại
                    // lstMapping danh sách trên form, curSdMappingColumnList danh sách lấy từ cơ sở dữ liệu
                    SD_MappingColumn temp = lstMapping.Find(model => model.MappingColumnId == column.MappingColumnId);

                    // Cập nhật Cột
                    if (temp != null && temp.Checked)
                    {
                        column.DestinationColumnName = temp.DestinationColumnName;
                        column.DestinationColumnType = temp.DestinationColumnType;
                        column.DestinationColumnNull = temp.DestinationColumnNull;
                        column.DestinationColumnSize = temp.DestinationColumnSize;
                        column.DestinationColumnPrecision = temp.DestinationColumnPrecision;
                        column.DestinationColumnScale = temp.DestinationColumnScale;
                        columnBO.Save(column);
                    }

                    // Xóa những cột
                    if (temp != null && !temp.Checked)
                    {
                        columnBO.Delete(temp.MappingColumnId);
                    }

                }

            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
            }

            return ReturnValue("Cập nhật thành công", true, null);
        }

        public class TempModel
        {
            public int Action { get; set; }
            public string TableName { get; set; }
            public int MappingTableId { get; set; }
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult SubmitChangeMappingTable(List<SD_MappingTable> sourceTableList)
        {
            try
            {
                SD_ConfigSource sourceDB = new SD_ConfigSourceBO().GetLatestConfigSource();
                SD_ConfigDestination destinationDB = new SD_ConfigDestinationBO().GetLatestConfigSource();

                string sourceConString = Utils.BuildConnectionString(sourceDB.ServerName, sourceDB.DataBaseName, sourceDB.sUser, sourceDB.sPassWord);

                SD_MappingTableBO mappingTableBO = new SD_MappingTableBO();

                foreach (SD_MappingTable table in sourceTableList)
                {
                    // Thêm mới
                    if (table.Checked && table.MappingTableId <= 0)
                    {
                        SD_MappingTable createdTable = mappingTableBO.Save(table);
                        SD_MappingColumnBO mappingColumnBO = new SD_MappingColumnBO(sourceConString);
                        List<SD_MappingColumn> sourceColumnList = mappingColumnBO.GetAllColumnInfoMetadataByTableName(createdTable.MappingTableId, table.SourceTable);
                        mappingColumnBO = new SD_MappingColumnBO();
                        foreach (SD_MappingColumn column in sourceColumnList)
                        {
                            column.ConfigSourceID = table.ConfigSourceID;
                            column.ConfigDestinationID = table.ConfigDestinationID;
                            column.SourceTable = table.SourceTable;
                            column.DestinationTable = table.DestinationTable;

                            column.DestinationColumnName = column.SourceColumnName;
                            column.DestinationColumnNull = column.SourceColumnNull;
                            column.DestinationColumnPrecision = column.SourceColumnPrecision;
                            column.DestinationColumnScale = column.SourceColumnScale;
                            column.DestinationColumnSize = column.SourceColumnSize;
                            column.DestinationColumnType = column.SourceColumnType;

                            column.MappingTableId = table.MappingTableId;
                            mappingColumnBO.Save(column);
                        }
                    }

                    // Xóa
                    if (!table.Checked && table.MappingTableId > 0)
                    {
                        mappingTableBO.DeleteSourceTable(table);
                    }
                }
                return ReturnValue("Cập nhật thành công", true);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return ReturnValue(ex.Message, false);
            }
        }

        //[ChildActionOnly]
        public ActionResult GetListSourceMappingTable()
        {
            try
            {
                SD_ConfigSource sourceDB = new SD_ConfigSourceBO().GetLatestConfigSource();
                SD_ConfigDestination destinationDB = new SD_ConfigDestinationBO().GetLatestConfigSource();
                //Get connectionString
                string sourceConString = Utils.BuildConnectionString(sourceDB.ServerName, sourceDB.DataBaseName, sourceDB.sUser, sourceDB.sPassWord);
                string destinationString = Utils.BuildConnectionString(destinationDB.ServerName, destinationDB.DataBaseName, destinationDB.sUser, destinationDB.sPassWord);


                //Lay danh sach cac table tu sourceMapping
                SD_MappingTableBO mapTableBo = new SD_MappingTableBO(sourceConString);
                List<SD_MappingTable> sourceTableList = mapTableBo.GetAllTableNameFromMetaData(sourceDB.ConfigSourceID);


                mapTableBo = new SD_MappingTableBO();
                SD_MappingTable mappingTable = null;
                foreach (SD_MappingTable sourceTable in sourceTableList)
                {
                    sourceTable.ConfigSourceID = sourceDB.ConfigSourceID;
                    sourceTable.ConfigDestinationID = destinationDB.ConfigDestinationID;

                    mappingTable = mapTableBo.GetMappingTableBySourceTable(sourceTable.ConfigSourceID, sourceTable.SourceTable);
                    if (mappingTable != null)
                    {
                        sourceTable.Checked = true;
                        sourceTable.MappingTableId = mappingTable.MappingTableId;
                        sourceTable.ConfigSourceID = mappingTable.ConfigSourceID;
                        sourceTable.ConfigDestinationID = mappingTable.ConfigDestinationID;
                        sourceTable.SourceTable = mappingTable.SourceTable;
                        sourceTable.DestinationTable = mappingTable.DestinationTable;
                    }
                }

                ViewBag.AreaList = sourceTableList;
                return PartialView("_ListSourceMappingTable", sourceDB);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return ReturnValue(ex.Message, false);
            }
        }
        #endregion

        #region mapping cột tới cột

        public ActionResult MappingDatabaseColumn()
        {

            return View();
        }
        public PartialViewResult GetListMappingTable()
        {
            try
            {
                SD_ConfigSource sourceDB = new SD_ConfigSourceBO().GetLatestConfigSource();
                SD_ConfigDestination destinationDB = new SD_ConfigDestinationBO().GetLatestConfigSource();
                //Get connectionString
                string sourceConString = Utils.BuildConnectionString(sourceDB.ServerName, sourceDB.DataBaseName, sourceDB.sUser, sourceDB.sPassWord);
                string destinationString = Utils.BuildConnectionString(destinationDB.ServerName, destinationDB.DataBaseName, destinationDB.sUser, destinationDB.sPassWord);


                //Lay danh sach cac table tu sourceMapping
                SD_MappingTableBO mapTableBo = new SD_MappingTableBO();
                List<SD_MappingTable> sourceMapping = mapTableBo.GetAllTableNameFromMapDB(sourceDB.ConfigSourceID);



                return PartialView("_ListMappingTable", sourceMapping);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                return null;
            }
        }
        #endregion

        public ActionResult TestConnection(string ServerName, string DatabaseName, string UserName, string Password)
        {
            try
            {
                string serverNameDecode = HttpUtility.UrlDecode(ServerName);
                string databaseNameDecode = HttpUtility.UrlDecode(DatabaseName);
                string userNameDecode = HttpUtility.UrlDecode(UserName);
                string passwordDecode = HttpUtility.UrlDecode(Password);


                string connString = Utils.BuildConnectionString(serverNameDecode, databaseNameDecode, userNameDecode, passwordDecode);
                if (Utils.TestConnection(connString))
                    return ReturnValue(Utils.GetString("Config_ConnectDBSuccess_Info"), true);
                else
                    return ReturnValue(Utils.GetString("Config_ConnectDBFailure_Info"), false);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

        [HttpPost]
        public ActionResult TestConnection(ServerConnectionModel data)
        {
            try
            {
                string serverNameDecode = HttpUtility.UrlDecode(data.ServerName);
                string databaseNameDecode = HttpUtility.UrlDecode(data.DatabaseName);
                string userNameDecode = HttpUtility.UrlDecode(data.UserName);
                string passwordDecode = HttpUtility.UrlDecode(data.Password);

                string connString = Utils.BuildConnectionString(serverNameDecode, databaseNameDecode, userNameDecode, passwordDecode);
                if (Utils.TestConnection(connString))
                    return ReturnValue(Utils.GetString("Config_ConnectDBSuccess_Info"), true);
                else
                    return ReturnValue(Utils.GetString("Config_ConnectDBFailure_Info"), false);
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToString());
                throw;
            }
        }

    }
}
